﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for ManagemetService
/// </summary>
public class ManagemetSubService
{
    SqlConnection coon;
	public ManagemetSubService()
	{
		//
		// TODO: Add constructor logic here
		//
        coon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
	}
    /*********** SUB SERVICE ***********/
    public DataSet GetCategory()
    {

        if (coon.State == ConnectionState.Closed)
            coon.Open();
        DataSet dt = new DataSet();
        string sql = "select * from SubCategory,ServiceCategory where SubCategory.ServiceCategoryID=ServiceCategory.ServiceCategoryID order by SubCategoryID DESC";
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);
        coon.Close();
        return dt;
    }
    public bool AddSubCategory(string name, string Description, int serviceID, string img)
    {

        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "insert into SubCategory values(@n,@d,@img,@s)";
        SqlCommand cmd = new SqlCommand(sql, coon);
        cmd.Parameters.AddWithValue("@n", name);
        cmd.Parameters.AddWithValue("@d", Description);
        cmd.Parameters.AddWithValue("@s", serviceID);
        cmd.Parameters.AddWithValue("@img", img);
        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;


    }

    public List<int> GetSubCategoryID()
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        List<int> l = new List<int>();

        string sql = "select SubCategoryID from SubCategory";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            l.Add(sdr.GetInt32(0));
        }
        sdr.Close();
        coon.Close();
        return l;
    }
    public DataSet GetServiceParent()
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();


        DataSet ds = new DataSet();
        string sql = "SELECT * FROM ServiceCategory";

        SqlDataAdapter sda1 = new SqlDataAdapter(sql, coon);
        sda1.Fill(ds);


        return ds;

    }


    public ORANGE_PROJECTModel.SubCategory GetSubService(int id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        ORANGE_PROJECTModel.SubCategory c = new ORANGE_PROJECTModel.SubCategory();
        string sql = "select * from SubCategory where SubCategoryID=" + id + "";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader s = cmd.ExecuteReader();
        while (s.Read())
        {
            c.SubCategoryID = s.GetInt32(0);
            c.SubName = s.GetString(1);
            c.Description = s.GetString(2);
            c.UrlImage = s.GetString(3);
            c.ServiceCategoryID = s.GetInt32(4);
        }
        coon.Close();
        return c;
    }

    public bool DeleteSubService(int id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();

        string sql_check = "select * from [Contract] where SubCategoryID = 7";

        SqlCommand cmd2 = new SqlCommand(sql_check, coon);


        SqlDataReader sda = cmd2.ExecuteReader();

        if (sda.HasRows)
        {
            sda.Close();
            return false;
        }

        sda.Close();


        string sql_method = "select * from PaymentMethod where SubCategoryID =" + id + "";
        SqlCommand cmd1 = new SqlCommand(sql_method, coon);
        SqlDataReader sda1 = cmd1.ExecuteReader();
        if (sda1.HasRows)
        {
            sda1.Close();
            return false;
        }
        sda1.Close();


        string sql = "DELETE FROM  SubCategory where SubCategoryID=" + id + "";
        SqlCommand cmd = new SqlCommand(sql, coon);
        int c = cmd.ExecuteNonQuery();
        return c > 0 ? true : false;




    }
    public bool UpdateSubService(int id, string name, string description, int parentcatID, string urlImage)
    {
        if (coon.State == ConnectionState.Closed)
        { coon.Open(); }

        string sql = "update SubCategory set SubName='" + name + "',UrlImage='" + urlImage + "',ServiceCategoryID=" + parentcatID + ",[Description]='" + description + "' where SubCategoryID = " + id + " ";
        SqlCommand cmd = new SqlCommand(sql, coon);

        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;
    }

    public DataSet SearchSubService(string s)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "select * from SubCategory,ServiceCategory where SubCategory.ServiceCategoryID=ServiceCategory.ServiceCategoryID and SubCategory.SubName like '%" + s + "%' ";
        DataSet dt = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);

        return dt;
    }
    public DataSet GetSubCategoryByCategoryID(int id)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM SubCategory join ServiceCategory on ServiceCategory.ServiceCategoryID = SubCategory.ServiceCategoryID WHERE SubCategory.ServiceCategoryID=" + id, coon);
        da.Fill(ds);
        return ds;
    }
    public DataSet GetSubcategoryByID(int subCategoryID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM SubCategory WHERE SubCategoryID=" + subCategoryID, coon);
        da.Fill(ds);
        return ds;
    }
    public int CheckTypeSubCategory(int subCategoryID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM PaymentMethod " +
                                            "JOIN PaymentMethodDetail " +
                                            "ON PaymentMethod.PaymentMethodID = PaymentMethodDetail.PaymentMethodID " +
                                            "WHERE PaymentMethod.SubCategoryID=" + subCategoryID, coon);
        da.Fill(ds);
        return ds.Tables[0].Rows.Count;
    }

    public DataTable GetDataSubcategoryByServiceID(string query)
    {
        DataTable dt = new DataTable();
        using (SqlCommand cmd = new SqlCommand(query, coon))
        {
            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = coon;
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }
    }
}